var express = require('express');
var productRouter = express.Router();
let connection = require('../db/db.js');
let SQLQuery = require('../utils/query.js');

/**创建产品接口
 * @param {productName} 产品名称
 * @param {price} 原价
 * @param {currentPrice} 现价
 * @param {productPicture} 产品图片
 * @param {placeOfOrigin} 产地     
 * @param {eatingPattern} 服用方式
 * @param {supplies} 主要成分
 * @param {MFG} 生产日期
 * @param {EXP} 保质日期
 * @param {barCode} 商品条形码
 * @param {dosageForm} 剂型
 * @param {spec} 规格
 * @param {brand} 品牌
 * @param {inventory} 库存
 * 
 * */
productRouter.post('/createProduct', async function (req, res) {
    let queryObj = req.body;
    var sql = 'insert into product(productName,price,currentPrice,productPicture,placeOfOrigin,eatingPattern,supplies,MFG,EXP,barCode,dosageForm,spec,brand,inventory,createTime,lastUpdateTime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
    var sqlParams = [queryObj.productName, queryObj.price, queryObj.currentPrice, queryObj.productPicture, queryObj.placeOfOrigin, queryObj.eatingPattern, queryObj.supplies, queryObj.MFG, queryObj.EXP, queryObj.barCode, queryObj.dosageForm, queryObj.spec, queryObj.brand, queryObj.inventory, new Date(), new Date()];
    try {
        await SQLQuery.query(sql, sqlParams);
        res.send(global.common.resJson(0, '添加成功！', null))
    } catch (err) {
        res.send(global.common.resJson(1, err, null))
    }
})

/**删除产品接口
 * @param {id} 列表id
 * */
productRouter.post('/deleteProduct', async function (req, res) {
    try {
        let queryObj = req.body;
        let sql = "update product set isDeleted = 1 where id = ?";
        let deleteSqlParams = [queryObj.id];
        let result = await SQLQuery.query(sql, deleteSqlParams);
        res.send(global.common.resJson(0, result.affectedRows === 1 ? '删除成功！' : '不存在', null));
    } catch (err) {
        res.send(err)
    }
})

/**编辑产品接口
 * @param {id} 列表id
 * @param {productName} 产品名称
 * @param {price} 原价
 * @param {currentPrice} 现价
 * @param {productPicture} 产品图片
 * @param {placeOfOrigin} 产地     
 * @param {eatingPattern} 服用方式
 * @param {supplies} 主要成分
 * @param {MFG} 生产日期
 * @param {EXP} 保质日期
 * @param {barCode} 商品条形码
 * @param {dosageForm} 剂型
 * @param {spec} 规格
 * @param {brand} 品牌
 * @param {inventory} 库存
 * */
productRouter.post('/updateProduct', async function (req, res) {
    try {
        let queryObj = req.body;
        let sql = "update product set productName = ? , price = ? ,currentPrice = ? ,productPicture = ? ,placeOfOrigin = ? ,eatingPattern = ? ,supplies = ?,MFG =?,EXP=?,barCode=?,dosageForm=?,spec=?,brand=?,inventory=?,lastUpdateTime =? where id = ?";
        let updateSqlParams = [queryObj.productName, queryObj.price, queryObj.currentPrice, queryObj.productPicture, queryObj.placeOfOrigin, queryObj.eatingPattern, queryObj.supplies, queryObj.MFG, queryObj.EXP, queryObj.barCode, queryObj.dosageForm, queryObj.spec, queryObj.brand, queryObj.inventory, new Date(), queryObj.id];
        let result = await SQLQuery.query(sql, updateSqlParams);
        res.send(global.common.resJson(0, result.affectedRows === 1 ? '修改成功！' : '不存在', null));
    } catch (err) {
        res.send(err)
    }
})

/**获取产品列表接口
 * @param {searchStr} 模糊查询字符串
 * @param {pageSize} 页尺寸
 * @param {pageIndex} 页码
 * 
 * */
productRouter.post('/productList', function (req, res) {

    let queryObj = req.body;

    let startNum = (queryObj.pageIndex - 1) * queryObj.pageSize;
    var sql = `SELECT id,productName,price,currentPrice,productPicture,placeOfOrigin,eatingPattern,supplies,MFG,EXP,barCode,dosageForm,spec,brand,inventory,createTime,lastUpdateTime FROM product WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0 LIMIT ${startNum},${queryObj.pageSize}`;

    connection.query(sql, function (err, result) {
        if (err) {
            res.send(global.common.resJson(1, '失败！', errall.code))
            return;
        }
        var allsql = `SELECT * FROM product WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0`;

        connection.query(allsql, function (errall, resultall) {
            if (errall) {
                res.send(global.common.resJson(1, '失败！', errall.code))
                return;
            }

            let resObj = {
                code: 0,
                message: 'success',
                totalNum: resultall.length,
                data: result
            }
            resJson = JSON.stringify(resObj)
            res.send(resJson);
        });
    })

})


/**获取产品列表接口-客户端
 * @param {searchStr} 模糊查询字符串
 * @param {pageSize} 页尺寸
 * @param {pageIndex} 页码
 * @param {category} 分类
 * */
// productRouter.post('/client/productList', async function (req, res) {
//     try {
//         let queryObj = req.body;
//         let startNum = (queryObj.pageIndex - 1) * queryObj.pageSize;
//         var sql = `SELECT id,productName,category,price,currentPrice,productPicture,placeOfOrigin,eatingPattern,supplies,MFG,EXP,barCode,dosageForm,spec,brand,inventory,createTime,lastUpdateTime FROM product WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0 AND (${queryObj.category} IS NULL OR category = ${queryObj.category}) LIMIT ${startNum},${queryObj.pageSize}`;
//         var allsql = `SELECT * FROM product WHERE productName LIKE '%${queryObj.searchStr}%' AND (${queryObj.category} IS NULL OR category = ${queryObj.category}) AND isDeleted = 0`;
//         let result = await SQLQuery.query(sql);
//         let resultall = await SQLQuery.query(allsql);
//         let resObj = {
//             code: 0,
//             message: 'success',
//             totalNum: resultall.length,
//             data: result
//         }
//         resJson = JSON.stringify(resObj)
//         res.send(resJson);
//     } catch (err) {
//         res.send(err)
//     }
// })

productRouter.post('/client/productList', async function (req, res) {
    try {
        let queryObj = req.body;
        let startNum = (queryObj.pageIndex - 1) * queryObj.pageSize;
        var sql = `SELECT id,productName,price,currentPrice,productPicture,placeOfOrigin,eatingPattern,supplies,MFG,EXP,barCode,dosageForm,spec,brand,inventory,createTime,lastUpdateTime FROM product WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0 LIMIT ${startNum},${queryObj.pageSize}`;
        var allsql = `SELECT * FROM product WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0`;
        let result = await SQLQuery.query(sql);
        let resultall = await SQLQuery.query(allsql);
        let resObj = {
            code: 0,
            message: 'success',
            totalNum: resultall.length,
            data: result
        }
        resJson = JSON.stringify(resObj)
        res.send(resJson);
    } catch (err) {
        res.send(err)
    }
})

/**
 * 根据产品分类获取产品列表
 * 
 * */
productRouter.post('/client/productListBySort', async function (req, res) {
    try {
        let queryObj = req.body;
        let startNum = (queryObj.pageIndex - 1) * queryObj.pageSize;
        if (queryObj.sort.length == 0) {
            var sql = `SELECT * FROM product_high_end WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0  LIMIT ${startNum},${queryObj.pageSize}`;
            var allsql = `SELECT * FROM product_high_end WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0`;
        } else {
            var sql = `SELECT * FROM product_high_end WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0 AND sort = '${queryObj.sort}' LIMIT ${startNum},${queryObj.pageSize}`;
            var allsql = `SELECT * FROM product_high_end WHERE productName LIKE '%${queryObj.searchStr}%' AND isDeleted = 0 AND sort = '${queryObj.sort}'`;
        }
        let result = await SQLQuery.query(sql, [queryObj.searchStr, startNum, queryObj.pageSize]);
        let resultall = await SQLQuery.query(allsql, [queryObj.pageSize]);
        let resObj = {
            code: 0,
            message: 'success',
            totalNum: resultall.length,
            data: result
        }
        resJson = JSON.stringify(resObj)
        res.send(resJson);
    } catch (err) {
        res.send(err)
    }
})

module.exports = productRouter;